﻿using System.Collections.Generic;
using System.Linq;
using EbbinghausWord.Common;
using EbbinghausWord.Entities;

namespace EbbinghausWord.DAL
{
    /// <summary>
    /// 发音文件数据访问类
    /// </summary>
    public class VoiceDAL
    {

        #region private field
        private static SysDataContext _sysDb = new SysDataContext();
        #endregion

        #region pulic method
        /// <summary>
        /// 添加发音文件
        /// </summary>
        /// <param name="tableVoiceEntity"></param>
        public void AddVoice(TableVoiceEntity tableVoiceEntity)
        {
            _sysDb.TableVoiceEntities.InsertOnSubmit(tableVoiceEntity);
            _sysDb.SubmitChanges();
        }

        /// <summary>
        /// 获得声音文件
        /// </summary>
        /// <param name="english"></param>
        /// <returns></returns>
        public byte[] GetVoiceStream(string english)
        {
            return _sysDb.TableVoiceEntities.First(x => x.English == english).Voice;
        }

        /// <summary>
        /// 指定单词的发音文件是否存在
        /// </summary>
        /// <param name="english"></param>
        /// <returns></returns>
        public bool IsVoiceExist(string english)
        {
            var et = new ExecuteTimer(false);
            var tmp = _sysDb.TableVoiceEntities.Count(x => x.English == english) != 0;
            et.Show("tmpIsVoiceExist");
            return tmp;
        }

        /// <summary>
        /// 删除指定的发音文件
        /// </summary>
        /// <param name="english"></param>
        /// <returns></returns>
        public void DeleteVoice(string english)
        {
            var voiceEntity = _sysDb.TableVoiceEntities.First(x => x.English == english);
            _sysDb.TableVoiceEntities.DeleteOnSubmit(voiceEntity);
            _sysDb.SubmitChanges();
        }

        /// <summary>
        /// 根据书籍Id获得需要下载的单词列表
        /// </summary>
        /// <param name="bookId"></param>
        /// <returns></returns>
        public List<string> GetEnglishForDownloadByBookId(int bookId)
        {
            List<string> hasDownloadWord = null;
            List<string> bookWord = null;

            //获得书籍对应的所有词典列表
            bookWord = BookDAL.GetAllBookWords().Where(x => x.BookId == bookId)
                .Select(x => x.English).ToList();

            //获得已经下载的声音文件列表
            hasDownloadWord = _sysDb.TableVoiceEntities.Select(x => x.English).ToList();

            return bookWord.GroupJoin(hasDownloadWord, x => x, x => x,
                               (x, y) => new { English = x, Count = y.Count() })
                .Where(x => x.Count == 0)
                .Select(x => x.English).ToList();
        }

        /// <summary>
        /// 获得所有已经下载发音的英语单词
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllDownloadedEnglish()
        {
            return _sysDb.TableVoiceEntities.Select(x => x.English).ToList();
        }
        #endregion
    }
}
